package cn.com.pxto.web.excel;

import java.io.File;
import java.io.IOException;

import cn.com.pxto.model.Guest;
import cn.com.pxto.model.Lesson;
import cn.com.pxto.model.LessonType;
import cn.com.pxto.model.School;
import cn.com.pxto.service.GuestManager;
import cn.com.pxto.service.SchoolManager;

import jxl.Cell;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {
	
	private  GuestManager guestManager;
	private  SchoolManager schoolManager;
	
	public void getRowCell(Sheet sheet ,int y) {
		Guest guest = new Guest();
		Integer ltid = null;
		for (int x = 0; x < 9; x++) {
			Cell cell = sheet.getCell(x, y);
			switch (x) {
			case 0:
				// logonDate
				DateCell dateCell = (DateCell) cell;
				guest.setLogonDate(dateCell.getDate().getTime());
				break;
			case 1:
				// name
				guest.setName(cell.getContents());
				break;
			case 2:
				// lessonTypeId
				ltid = setGuestLessonTypeId(guest, cell);
				break;
			case 3:
				// lessonId
			    setGuestLessonId(guest, cell, ltid);
				guest.setLesson(cell.getContents());
				break;
			case 4:
				// schoolId
				setGuestSchoolId(guest, ltid, cell);
				break;
			case 5:
				// remark;
				guest.setRemark(cell.getContents());
				break;
			case 6:
				// tel
				guest.setTel(cell.getContents());
				break;
			case 7:
//				revisit time
//				DateCell dateCell2 = (DateCell) cell;
//				guest.setStartClassTime(dateCell2.getDate().getTime());
				break;
			case 8:
//				type
				//TODO
				guest.setSignType(new Integer(0));
				break;
			}
		}
		guest.setUserId(2);
		guestManager.createGuest(guest);
		System.out.println("sucess:" + guest.getName());
	}

	private  void setGuestSchoolId(Guest guest, Integer ltid, Cell cell) {
		String schoolName = cell.getContents();
		School school = schoolManager.getSchoolByName(schoolName);
		if (school == null) {
			school = new School();
			school.setName(schoolName);
			// TODO school lessonTypeStr
			school.setLessonTypeStr(","+ltid + ",");
			schoolManager.createSchool(school);
		} else {
			// TODO school lessonTypeStr
			String str = school.getLessonTypeStr();
			if (str.indexOf(ltid + ",") == -1) {
				str += ltid + ",";
				school.setLessonTypeStr(str);
				schoolManager.updateSchool(school);
			}
		}
		guest.setSchoolId(school.getSid());
	}

	private  void setGuestLessonId(Guest guest, Cell cell, Integer ltid) {
		String lessonName = cell.getContents();
		Lesson lesson = schoolManager.getLessonByName(lessonName);
		if (lesson == null) {
			lesson = new Lesson();
			lesson.setName(lessonName);
			lesson.setType(ltid);
			schoolManager.createLesson(lesson);
		}
	}

	private  Integer setGuestLessonTypeId(Guest guest, Cell cell) {
		String lessonTypeName = cell.getContents();
		LessonType lessonType = schoolManager
				.getLessonTypeByName(lessonTypeName);
		if (lessonType == null) {
			lessonType = new LessonType();
			lessonType.setName(lessonTypeName);
			schoolManager.createLessonType(lessonType);
		}
		guest.setLessonTypeId(lessonType.getLtid());
		return lessonType.getLtid();
	}
	
	
	public void readExcelSheet(String filePath) throws BiffException,
			IOException {
		// 通过Workbook的静态方法getWorkbook选取Excel文件
		Workbook workbook = Workbook.getWorkbook(new File(filePath));

		// 通过Workbook的getSheet方法选择第一个工作簿（从0开始）
		Sheet sheet = workbook.getSheet(0);
		int y = 2;
		do {
			try {
				for (int x = 0; x < 9; x++) {
					Cell cell = sheet.getCell(x, y);
					System.out.println(cell.getContents());
				}
				y++;
			} catch (ArrayIndexOutOfBoundsException ex) {
				System.out.println("end");
				y = 0;
			}
		} while (y != 0);

	}
	
	public void loadExcelToDb(String filePath) throws BiffException, IOException{
		// 通过Workbook的静态方法getWorkbook选取Excel文件
		Workbook workbook = Workbook.getWorkbook(new File(filePath));
		// 通过Workbook的getSheet方法选择第一个工作簿（从0开始）
		Sheet sheet = workbook.getSheet(0);
		int y = 2;
		do {
			try {
				getRowCell(sheet,y);
				y++;
			} catch (ArrayIndexOutOfBoundsException ex) {
				ex.printStackTrace();
				System.out.println("end");
				y = 0;
			}
		} while (y != 0);

	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		ReadExcel re = new ReadExcel();
		try {
			String filePath = ReadExcel.class.getResource("/test/default.xls")
					.getPath();
			System.out.println(filePath);
			re.readExcelSheet(filePath);
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public void setGuestManager(GuestManager guestManager) {
		this.guestManager = guestManager;
	}

	public void setSchoolManager(SchoolManager schoolManager) {
		this.schoolManager = schoolManager;
	}

}
